package com.BlackMesa.database;

import java.util.ArrayList;
import java.util.Date;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.graphics.Color;
import android.util.Log;

import com.BlackMesa.models.*;
import com.google.android.maps.GeoPoint;

public class DatabaseAdapter {

	private SQLiteDatabase database;
	private DatabaseCreator creator;
	private String[] userColumns = { "user_id", "username", "name", "email",
			"pass" };
	private String[] taskColumns = { "task_id", "name", "dueDate",
			"detailedDescription", "isComplete", "location_id", "category_id",
			"user_id", "dueTime" };
	private String[] locColumns = { "location_id", "lat", "lon", "name",
			"user_id" };
	private String[] categoryColumns;

	public DatabaseAdapter(Context context) {
		creator = new DatabaseCreator(context);
	}

	/**
	 * opens the database in order to write to it
	 */
	public void open() {
		database = creator.getWritableDatabase();
	}

	/**
	 * closes the database connection
	 */
	public void close() {
		creator.close();
	}

	private void init(int id) {
		ContentValues allTask = new ContentValues();
		allTask.put("category", "dummy");
		allTask.put("color", 0);
		if (id == 0) {
			allTask.put("user_id", 1);
		} else {
			allTask.put("user_id", id);
		}
		Long test = database.insert("CATEGORIES", null, allTask);
		Log.d("init1", test.toString());
		ContentValues dumLoc = new ContentValues();
		dumLoc.put("lat", 0);
		dumLoc.put("lon", 0);
		dumLoc.put("name", "dummy");
		dumLoc.put("user_id", id);

	}

	/**
	 * inserts a user into the table
	 * 
	 * @param user
	 *            the user that is being inserted in to the table
	 * @return the user in the table
	 */
	public User createUser(User user) {
		this.open();
		ContentValues values = new ContentValues();
		values.put("name", user.getName());
		values.put("username", user.getUsername());
		values.put("email", user.getEmail());
		values.put("pass", user.getPassword());
		database.insert("USERS", null, values);
		Cursor cursor = database.rawQuery("SELECT user_id FROM USERS", null);
		cursor.moveToLast();
		user.setId(cursor.getInt(0));
		cursor.close();
		this.close();
		return user;

	}
	
	

	/**
	 * inserts a task into the table
	 * 
	 * @param task
	 *            the task that is being inserted in to the table
	 * @return the task in the table
	 */
	public Task createTask(Task task) {
		this.open();
		ContentValues values = new ContentValues();
		// values.put("task_id", "NULL");
		values.put("name", task.getName());
		if (task.getDueDate() != null) {
			values.put("dueDate", task.getDueDate().toString());
		}
		if (task.getDueTime() != null) {
			values.put("dueTime", task.getDueTime().toString());
		}
		values.put("detailedDescription", task.getDetailedDescription());
		values.put("user_id", task.getUserid());
		if (task.getCategory() == null) {
			values.put("category_id", 1);
		} else {
			values.put("category_id", task.getCategory().getId());
		}
		if (task.isComplete()) {
			values.put("isComplete", "true");
		} else {
			values.put("isComplete", "false");
		}
		if (task.getLocation() != null) {
			values.put("location_id", task.getLocation().getId());
		} else {
			values.put("location_id", 1);
		}
		Log.d(values.toString(), "hello");
		Long blah = database.insert("TASKS", null, values);
		Log.d("error?", blah.toString());
		Cursor cursor = database.rawQuery("SELECT task_id FROM TASKS", null);
		cursor.moveToLast();
		task.setId(cursor.getInt(0));
		cursor.close();
		this.close();
		if (task.getLocation() != null) {
			createLocation(task.getLocation(), task.getUserid());
		}
		return task;

	}

	public OurLocation createLocation(OurLocation location, int user_id) {
		this.open();
		ContentValues values = new ContentValues();
		values.put("lat", location.getLoc().getLatitudeE6());
		values.put("lon", location.getLoc().getLongitudeE6());
		values.put("name", location.getName());
		values.put("user_id", user_id);
		database.insert("LOCATIONS", null, values);
		Cursor cursor = database.rawQuery("SELECT location_id FROM LOCATIONS",
				null);
		cursor.moveToLast();
		location.setId(cursor.getInt(0));
		//int t = cursor.getInt(0);
		cursor.close();
		this.close();
		return null;
	}
	
	public ArrayList<Task> getAll(User user) {
		this.open();
		ArrayList<Task> tasks = new ArrayList<Task>();
		Cursor c = database.rawQuery("SELECT * FROM TASKS WHERE user_id = " + user.getId(), null);
		c.moveToFirst();
		while (!c.isAfterLast()) {
			tasks.add(cursorToTask(c));
			c.moveToNext();
		}
		c.close();
		this.close();
		return tasks;
	}

	public int createCategory(Category category) {
		this.open();
		ContentValues values = new ContentValues();
		values.put("category", category.getName());
		values.put("color", category.getColor());
		if (category.getUserid() == 0) {
			values.put("user_id", 1);
		} else {
			values.put("user_id", category.getUserid());
		}
		database.insert("CATEGORIES", null, values);
		Cursor cursor = database.rawQuery("SELECT category_id FROM CATEGORIES",
				null);
		cursor.moveToLast();
		category.setId(cursor.getInt(0));
		int t = cursor.getInt(0);
		cursor.close();
		this.close();
		return t;
	}


	/**
	 * creates a list of all the users in the database
	 * 
	 * @return all the users in the database
	 */
	public ArrayList<User> getUsers() {
		this.open();
		ArrayList<User> users = new ArrayList<User>();
		Cursor cursor = database.rawQuery("SELECT * FROM USERS", null);
		cursor.moveToFirst();
		while (!cursor.isAfterLast()) {
			users.add(cursorToUser(cursor));
			cursor.moveToNext();
		}
		// Make sure to close the cursor
		cursor.close();
		this.close();
		return users;
	}

	/**
	 * gets all the tasks of a category
	 * 
	 * @param category
	 * @return
	 */
	public ArrayList<Task> getTasks() {
		this.open();
		ArrayList<Task> tasks = new ArrayList<Task>();
		Cursor cursor = database.query("TASKS", taskColumns, null, null, null,
				null, null);
		cursor.moveToFirst();
		while (!cursor.isAfterLast()) {
			tasks.add(cursorToTask(cursor));
			cursor.moveToNext();
		}
		// Make sure to close the cursor
		cursor.close();
		this.close();
		return tasks;
	}
	
	

	public ArrayList<OurLocation> getLocations(int id) {
		this.open();
		ArrayList<OurLocation> loc = new ArrayList<OurLocation>();
		Cursor cursor = database.query("Locations", locColumns, null, null,
				null, null, null);
		cursor.moveToFirst();
		while (!cursor.isAfterLast()) {
			loc.add(cursorToLocation(cursor));
			cursor.moveToNext();
		}
		// Make sure to close the cursor
		cursor.close();
		this.close();
		return loc;
	}

	/**
	 * gets all the tasks of a category
	 * 
	 * @param category
	 * @return
	 */
	public ArrayList<Task> getTasks(Category category) {
		ArrayList<Task> tasks = new ArrayList<Task>();
		Cursor cursor = database.rawQuery(
				"SELECT * FROM TASKS WHERE category_id =" + category.getId(),
				null);
		cursor.moveToFirst();
		while (!cursor.isAfterLast()) {
			tasks.add(cursorToTask(cursor));
			cursor.moveToNext();
		}
		// Make sure to close the cursor
		cursor.close();
		return tasks;
	}

	public ArrayList<Category> getCategories(int id) {
		this.open();
		Cursor cur = database.rawQuery(
				"SELECT * FROM CATEGORIES WHERE user_id =" + id, null);
		Log.d("catnum", Integer.toString(cur.getCount()));
		if (cur.getCount() < 1) {
			init(id);

		}
		cur.close();
		Cursor test = database.rawQuery("SELECT * FROM TASKS", null);
		Log.d("cursor", test.toString());
		test.moveToFirst();
		while (!test.isAfterLast()) {
			Log.d("cusors", cursorToStringTask(test));
			test.moveToNext();
		}
		test.close();

		Cursor test1 = database.rawQuery("SELECT * FROM CATEGORIES", null);
		Log.d("cursor", test1.toString());
		test1.moveToFirst();
		while (!test1.isAfterLast()) {
			Log.d("categories", cursorToStringCat(test1));
			test1.moveToNext();

		}
		test1.close();

		Cursor test11 = database.rawQuery("SELECT * FROM USERS", null);
		Log.d("cursor", test11.toString());
		test11.moveToFirst();
		while (!test11.isAfterLast()) {
			Log.d("Users", cursorToStringUser(test11));
			test11.moveToNext();

		}
		test11.close();
		ArrayList<Category> categories = new ArrayList<Category>();
		Log.d("idnum", Integer.toString(id));
		Cursor cursor = database.rawQuery(
				"SELECT * FROM CATEGORIES WHERE user_id =" + id, null);
		Log.d("matchnum", Integer.toString(cursor.getCount()));
		cursor.moveToFirst();
		while (!cursor.isAfterLast()) {
			categories.add(cursorToCategory(cursor));
			cursor.moveToNext();
		}
		// Make sure to close the cursor
		cursor.close();

		Log.d("catshere", Integer.toString(categories.size()));
		for (Category c : categories) {
			c.setTasks(getTasks(c));
		}
		this.close();
		return categories;
	}

	/**
	 * updates a task in the database
	 */
	public void editTask(Task task) {
		deleteTask(task.getId());
		createTask(task);

	}

	public Category editCategory(Category category) {
		
		this.open();
		Cursor cur = database.rawQuery("SELECT * FROM CATEGORIES WHERE category_id =" + category.getId(), null);
		if (cur.getCount() < 1) {
			createCategory(category);
		}
		cur.close();
		Cursor c = database.rawQuery("UPDATE CATEGORIES SET category = \""+category.getName()+"\", color = "+category.getColor()+" WHERE category_id =" + category.getId(), null);
		this.close();
		return category;
	}

	public OurLocation editLocation(OurLocation loc) {
		return null;
	}

	public void deleteTask(int id) {
		this.open();
		database.rawQuery("DELETE FROM TASKS WHERE task_id =" + id,
				null);
		this.close();
	}
	
	public void deleteCategory(int id) {
		this.open();
		database.rawQuery("DELETE FROM CATEGORIES WHERE category_id ="
				+ id, null);
		this.close();
	}

	public void switchComplete(Task task) {
		this.open();
		ContentValues v = new ContentValues();	
		if (task.isComplete()) {
			v.put("isComplete", "true");
		} else {
			v.put("isComplete", "false");
		}
		//database.rawQuery("UPDATE TASKS SET isComplete = \"" + v + "\" WHERE task_id = " + task.getId(), null);
		//database.rawQuery("UPDATE TASKS SET isComplete=\"true\"", null);
		database.update("TASKS", v, "task_id =" + task.getId(), null);
		this.close();
	}

	/**
	 * converts a row from the user table to an actual user
	 * 
	 * @param c
	 *            the cursor representation of a user in the table
	 * @return the user
	 */
	private User cursorToUser(Cursor c) {
		User user = new User(c.getInt(0), c.getString(2), c.getString(1),
				c.getString(3), c.getString(4), this);
		user.setCategories(getCategories(c.getInt(0)));
		return user;
	}

	/**
	 * converts a row from the user table to an actual user
	 * 
	 * @param cursor
	 *            the cursor representation of a user in the table
	 * @return the user
	 */
	private Task cursorToTask(Cursor c) {

		// Cursor locCur =
		// database.rawQuery("SELECT * FROM LOCATIONS WHERE location_id =" +
		// c.getInt(5), null);
		// locCur.moveToFirst();
		// OurLocation loc = cursorToLocation(locCur);
		// locCur.close();

		Task t = new Task(c.getInt(0), c.getString(1), c.getString(2),
				c.getString(3), c.getString(4), c.getInt(5), c.getInt(6),
				c.getInt(7), c.getString(8), this);
		
		return t;
		// Task t = new Task(cursor.getString(1), cursor.getString(2),
		// cursor.getString(3), cursor.getString(4), cursor.getString(5));
		// t.setId(cursor.getInt(0));
		// return t;
	}
	
	private OurLocation cursorToLocation(Cursor cursor) {
		OurLocation loc = new OurLocation(cursor.getString(3), new GeoPoint(
				cursor.getInt(1), cursor.getInt(2)));
		loc.setId(cursor.getInt(0));
		return loc;
	}

	public ArrayList<Task> getIncomplete(int id) {
		ArrayList<Task> tasks = new ArrayList<Task>();
		Cursor cursor = database.rawQuery(
				"SELECT * FROM TASKS WHERE isComplete = \"false\" ",
				null);
		cursor.moveToFirst();
		while (!cursor.isAfterLast()) {
			tasks.add(cursorToTask(cursor));
			cursor.moveToNext();
		}
		// Make sure to close the cursor
		cursor.close();
		return tasks;
	}

	public Category getCategory(int id) {
		Cursor c = database.rawQuery(
				"SELECT * FROM CATEGORIES WHERE category_id =" + id, null);
		Log.d("idhere", Integer.toString(id));
		c.moveToFirst();
		Category u = cursorToCategory(c);
		c.close();
		return u;
	}

	public User getUser(int id) {
		Cursor c = database.rawQuery(
				"SELECT * FROM USERS WHERE user_id =" + id, null);
		c.moveToFirst();
		User u = cursorToUser(c);
		c.close();
		return u;
	}

	public OurLocation getLocation(int id) {
		Cursor c = database.rawQuery(
				"SELECT * FROM CATEGORIES WHERE category_id =" + id, null);
		if (c.getCount() == 0) {
			c.close();
			return new OurLocation("dummy", new GeoPoint(0,0));
		}
		c.moveToFirst();
		OurLocation u = cursorToLocation(c);
		c.close();
		return u;
	}

	private Category cursorToCategory(Cursor cursor) {
		Category cat = new Category(cursor.getString(1), cursor.getInt(2));
		cat.setUserid(cursor.getInt(3));
		cat.setId(cursor.getInt(0));
		return cat;
	}

	private String cursorToStringCat(Cursor c) {
		return Integer.toString(c.getInt(0)) + " " + c.getString(1) + " "
				+ Integer.toString(c.getInt(2)) + " "
				+ Integer.toString(c.getInt(3));
	}

	private String cursorToStringTask(Cursor c) {
		return Integer.toString(c.getInt(0)) + " " + c.getString(1) + " "
				+ c.getString(2) + " " + c.getString(3) + " " + c.getString(4)
				+ " " + Integer.toString(c.getInt(5)) + " "
				+ Integer.toString(c.getInt(6)) + " "
				+ Integer.toString(c.getInt(7)) + " " + c.getString(8);
	}

	private String cursorToStringUser(Cursor c) {
		return Integer.toString(c.getInt(0)) + " " + c.getString(1) + " "
				+ c.getString(2) + " " + c.getString(3) + " " + c.getString(4);
	}
}
